lei.cs_SQL_C#读写类

160行_lei.cs_SQL_C#读写类

using System;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;

namespace ASPNETAJAXWeb.AjaxLeaveword
{
public class Message
{
public Message()
{
///
}

public DataSet GetMessages()
{///获取连接字符串
string connectionString = ConfigurationManager.ConnectionStrings["SQLCONNECTIONSTRING"].ConnectionString;
///创建连接
SqlConnection con = new SqlConnection(connectionString);
///创建SQL语句
string cmdText = "SELECT * FROM Message Order by CreateDate DESC";
///创建SqlDataAdapter
SqlDataAdapter da = new SqlDataAdapter(cmdText,con);
///定义DataSet
DataSet ds = new DataSet();
try
{   ///打开连接
con.Open();
///填充数据
da.Fill(ds,"DataTable");
}
catch(Exception ex)
{   ///抛出异常
throw new Exception(ex.Message,ex);
}
finally
{   ///关闭连接
con.Close();
}

return ds;
}/

public int AddMessage(string title,string message,string ip,string email)
{///获取连接字符串
string connectionString = ConfigurationManager.ConnectionStrings["SQLCONNECTIONSTRING"].ConnectionString;
///创建连接
SqlConnection con = new SqlConnection(connectionString);
///创建SQL语句
string cmdText = "INSERT INTO Message(Title,Message,IP,Email,CreateDate,Status)VALUES(@Title,@Message,@IP,@Email,GETDATE(),0)";
///创建SqlCommand
SqlCommand cmd = new SqlCommand(cmdText,con);
///创建参数并赋值
cmd.Parameters.Add("@Title",SqlDbType.VarChar,200);
cmd.Parameters.Add("@Message",SqlDbType.Text);
cmd.Parameters.Add("@Ip",SqlDbType.VarChar,20);
cmd.Parameters.Add("@Email",SqlDbType.VarChar,255);
cmd.Parameters[0].Value = title;
cmd.Parameters[1].Value = message;
cmd.Parameters[2].Value = ip;
cmd.Parameters[3].Value = email;
int result = -1;
try
{   ///打开连接
con.Open();
///操作数据
result = cmd.ExecuteNonQuery();
}
catch(Exception ex)
{   ///抛出异常
throw new Exception(ex.Message,ex);
}
finally
{   ///关闭连接
con.Close();
}

return result;
}

public int DeleteMessage(int messageID)
{///获取连接字符串
string connectionString = ConfigurationManager.ConnectionStrings["SQLCONNECTIONSTRING"].ConnectionString;
///创建连接
SqlConnection con = new SqlConnection(connectionString);
///创建SQL语句
string cmdText = "DELETE Message WHERE ID = @ID";
///创建SqlCommand
SqlCommand cmd = new SqlCommand(cmdText,con);
///创建参数并赋值
cmd.Parameters.Add("@ID",SqlDbType.Int,4);
cmd.Parameters[0].Value = messageID;

int result = -1;
try
{   ///打开连接
con.Open();
///操作数据
result = cmd.ExecuteNonQuery();
}
catch(Exception ex)
{   ///抛出异常
throw new Exception(ex.Message,ex);
}
finally
{   ///关闭连接
con.Close();
}

return result;
}

public DataSet GetReplyByMessage(int messageID)
{///获取连接字符串
string connectionString = ConfigurationManager.ConnectionStrings["SQLCONNECTIONSTRING"].ConnectionString;
///创建连接
SqlConnection con = new SqlConnection(connectionString);
///创建SQL语句
string cmdText = "SELECT * FROM Reply WHERE MessageID = @MessageID Order by CreateDate DESC";
///创建SqlDataAdapter
SqlDataAdapter da = new SqlDataAdapter(cmdText,con);
///创建参数并赋值
da.SelectCommand.Parameters.Add("@MessageID",SqlDbType.Int,4);
da.SelectCommand.Parameters[0].Value = messageID;
///定义DataSet
DataSet ds = new DataSet();
try
{   ///打开连接
con.Open();
///填充数据
da.Fill(ds,"DataTable");
}
catch(Exception ex)
{   ///抛出异常
throw new Exception(ex.Message,ex);
}
finally
{   ///关闭连接
con.Close();
}

return ds;
}

public int AddReply(string message,string ip,int messageID)
{///获取连接字符串
string connectionString = ConfigurationManager.ConnectionStrings["SQLCONNECTIONSTRING"].ConnectionString;
///创建连接
SqlConnection con = new SqlConnection(connectionString);
///创建SQL语句
string cmdText = "INSERT INTO Reply(Reply,IP,CreateDate,MessageID)VALUES(@Reply,@IP,GETDATE(),@MessageID)";
///创建SqlCommand
SqlCommand cmd = new SqlCommand(cmdText,con);
///创建参数并赋值
cmd.Parameters.Add("@Reply",SqlDbType.VarChar,1000);
cmd.Parameters.Add("@Ip",SqlDbType.VarChar,20);
cmd.Parameters.Add("@MessageID",SqlDbType.Int,4);
cmd.Parameters[0].Value = message;
cmd.Parameters[1].Value = ip;
cmd.Parameters[2].Value = messageID;

int result = -1;
try
{   ///打开连接
con.Open();
///操作数据
result = cmd.ExecuteNonQuery();
}
catch(Exception ex)
{   ///抛出异常
throw new Exception(ex.Message,ex);
}
finally
{   ///关闭连接
con.Close();
}

return result;
}
}
}