从日历中获取所选日期并将其保存到SQL服务器

本文关键字：保存 服务器 SQL 日期 日历 获取 | 更新日期: 2023-09-27 18:16:02

我想从monthCalender1获得选定的日期，并从SQL Server表检索我的数据。但是我得到异常消息

无法转换为日期时间格式

SQL Server表日期格式为"dd/mm/yyyy"

private void monthCalendar1_DateChanged(object sender, DateRangeEventArgs e)
{
          rtbDocument.Clear();
          SqlConnection cn = null;
          SqlCommand cmd = null;
          SqlDataReader reader = null;
          string date = monthCalendar1.SelectionRange.ToString();
          System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-CA");
          DateTime dt = DateTime.Parse(date); //uses the current Thread's culture
          CultureInfo provider = CultureInfo.InvariantCulture;
          textBox1.Text = date; 
          try
          {
            cn = new SqlConnection(@"Data Source=.'SQLEXPRESS;AttachDbFilename=C:'Users'User'Desktop'myproject_c#'diary'photogallery'RicherTextBox_src'RicherTextBox'diary.mdf;Integrated Security=True;User Instance=True;");
            cn.Open();
            cmd = new SqlCommand("SELECT rtf_file_content FROM rtf WHERE rtf_date=@dt AND user_rtf_id=1", cn);
            cmd.Parameters.AddWithValue("@dt", dt);
            reader = cmd.ExecuteReader();
            reader.Read();
            if (reader.HasRows)
            {
              if (!reader.IsDBNull(0))
              {
                Byte[] rtf = new Byte[Convert.ToInt32((reader.GetBytes(0, 0,
                                                       null, 0, Int32.MaxValue)))];
                long bytesReceived = reader.GetBytes(0, 0, rtf, 0, rtf.Length);
                ASCIIEncoding encoding = new ASCIIEncoding();
                rtbDocument.Rtf = encoding.GetString(rtf, 0, Convert.ToInt32(bytesReceived));
              }
            }
          }
          catch(Exception ex)
          {
            MessageBox.Show(ex.Message);
          }
          finally
          {
            if (null != reader) reader.Close();
            if (null != cn) cn.Close();
          }
        }

从日历中获取所选日期并将其保存到SQL服务器

不要一直将日期转换为字符串(然后再下两行将其解析回DateTime ....) -停止这样做!按原样使用DateTime…也:DATETIME在SQL Server没有格式-它是一个8字节的二进制值-它只得到一个格式化当你需要输出它作为一个字符串(或解析它从一个字符串-但尽量避免，尽可能多的!)。

把你的代码改成这样:

cmd = new SqlCommand("SELECT rtf_file_content FROM rtf WHERE rtf_date=@dt AND user_rtf_id=1", cn);
cmd.Parameters.Add("@dt", SqlDbType.DateTime).Value = monthCalendar1.SelectionStart;

，这就是你所需要做的——不要弄乱字符串格式的日期字面值或任何类似的东西——只要使用日期就可以了!

我还建议将您的SqlConnection, SqlCommand和SqlDataReader变量放入using(..) { ... }块中，以确保使用后立即正确处理，并且阅读器的处理也有点不稳定.....

我将使用这段代码作为一个整体:

private void monthCalendar1_DateChanged(object sender, DateRangeEventArgs e)
{
    rtbDocument.Clear();
    // set up your connection string (typically from a config) and query text
    string connString = @"Data Source=.'SQLEXPRESS;AttachDbFilename=C:'Users'User'Desktop'myproject_c#'diary'photogallery'RicherTextBox_src'RicherTextBox'diary.mdf;Integrated Security=True;User Instance=True;";
    string query = "SELECT rtf_file_content FROM rtf WHERE rtf_date=@dt AND user_rtf_id=1";
    // set up connection and command - both are disposable, put them in a "using" block
    using (SqlConnection cn = new SqlConnection(connString))
    using (SqlCommand cmd = new SqlCommand(query, conn))
    {
        // set up parameter for query
        // define the parameter to be a "DateTime" parameter
        // set value directly from a "DateTime" property of your "monthCalendar1"
        cmd.Parameters.Add("@dt", SqlDbType.DateTime).Value = monthCalendar1.SelectionStart;
        // open connection, execute reader...
        conn.Open();
        using (SqlDataReader reader = cmd.ExecuteReader())
        {
            // loop over reader - reading all rows being returned by query
            while (reader.Read())
            {
                if (!reader.IsDBNull(0))
                {
                     Byte[] rtf = new Byte[Convert.ToInt32((reader.GetBytes(0, 0, null, 0, Int32.MaxValue)))];
                     long bytesReceived = reader.GetBytes(0, 0, rtf, 0, rtf.Length);
                     ASCIIEncoding encoding = new ASCIIEncoding();
                     rtbDocument.Rtf = encoding.GetString(rtf, 0, Convert.ToInt32(bytesReceived));
                }
            }
        }
    }
}
相关文章：