Inserting DateTime in Database as a string in a DateTime datatype in SQL Server can be
stored wrong in the database and hence can retreive wrong from the database.
For eg: Below Date is
DateTime dt = new DateTime(2009, 5, 10);
Which is May 10 2009
But when stored as string in DateTime datatype can be stored as October 5 2009
To solve this We can use SqlParameter Class
which code is given below in
StoreDateTimeasDateTime Function
namespace WindowsFormsApplication1
{
public partial class Form2 : Form
{
SqlConnection con = new SqlConnection("Data Source=PC;Initial Catalog=SampleDB;User Id=sa;Password=sa2005");
public Form2()
{
InitializeComponent();
}
private void Form2_Load(object sender, EventArgs e)
{
}
private void StoreDateTimeasString()
{
DateTime dt = new DateTime(2009, 5, 10);
con.Open();
SqlCommand cmd = new SqlCommand("insert into Sample1 values('1','" + dt.ToShortDateString() + "')",con);
int rupdate = cmd.ExecuteNonQuery();
if (rupdate == 1)
{
MessageBox.Show("Rows Updated");
}
else
{
MessageBox.Show("Error");
}
con.Close();
}
private void StoreDateTimeasDateTime()
{
DateTime dt = new DateTime(2009, 5, 10);
con.Open();
SqlCommand cmd = new SqlCommand("insert into Sample1 values(@Id,@DateTime)", con);
//declare parameters
cmd.Parameters.Add(new SqlParameter("@Id", SqlDbType.VarChar));
cmd.Parameters.Add(new SqlParameter("@DateTime", SqlDbType.DateTime));
//Assign parameters
cmd.Parameters["@Id"].Value = "2";
cmd.Parameters["@DateTime"].Value = dt;
int rupdate = cmd.ExecuteNonQuery();
if (rupdate == 1)
{
MessageBox.Show("Rows Updated");
}
else
{
MessageBox.Show("Error");
}
con.Close();
}
private void btnsds_Click(object sender, EventArgs e)
{
StoreDateTimeasString();
}
private void btnsdd_Click(object sender, EventArgs e)
{
StoreDateTimeasDateTime();
}
private void RetrievefromDatabase()
{
DateTime datetime = new DateTime();
con.Open();
SqlCommand cmd = new SqlCommand("select * from sample1", con);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
textBox1.Text += dr.GetString(0) + " "+dr.GetDateTime(1).ToLongDateString() + Environment.NewLine;
datetime=dr.GetDateTime(1);
textBox1.Text += datetime.ToLongDateString() + Environment.NewLine;
}
con.Close();
}
private void btnrfd_Click(object sender, EventArgs e)
{
RetrievefromDatabase();
}
}
}
Thanks for Reading
Mahendra
