c connects to the sqlserver database inserts data and gets time samples from the database
- 2020-06-23 01:44:46
- OfStack
c# connects to sqlserver, inserts data, gets time from the database
using System;
using System.Data.SqlClient;
namespace Test
{
// Connect to database
public class Connection
{
private static string connectionString =
"Server = 192.168.1.222;" +
"Database = Test;" +
"User ID = Test;" +
"Password = abc123;";
/// <summary>
/// Connect to database
/// </summary>
/// <returns></returns>
private SqlConnection ConnectionOpen()
{
SqlConnection conn = new SqlConnection(connectionString);
conn.Open();
return conn;
}
/// <summary>
/// To the table (Table) insert 1 The data
/// </summary>
public void Insert(string value1, string value2, string value3, DateTime dateTime)
{
SqlConnection conn = ConnectionOpen();
string sql =
"insert into Table(row1, row2, row3, DateTime) values ('" +
value1 + "', '" + value2 + "', '" + value3 + "', '" + dateTime + "')";
SqlCommand comm = new SqlCommand(sql, conn);
comm.ExecuteReader();
conn.Close();
}
/// <summary>
/// Gets the current time from the database
/// </summary>
/// <returns></returns>
public DateTime GetDateTimeFromSQL()
{
SqlConnection conn = ConnectionOpen();
string sql = "select getdate()";
SqlCommand comm = new SqlCommand(sql, conn);
SqlDataReader reader = comm.ExecuteReader();
DateTime dt;
if (reader.Read())
{
dt = (DateTime)reader[0];
conn.Close();
return dt;
}
conn.Close();
return DateTime.MinValue;
}
}
}
c# connects to the SQL Server 2008 example
/*
* instructions
* Functional specifications : Data access encapsulation. All data is defined through this class dbConnection Access the database.
* At the same time, the generic is defined cmd , as well as cmd Common methods of accessing stored procedures RunPro
*
* The author : RogerWang
*
* Creation date :2006-02-15
*
*/
using System;
using System.Data;
using System.Data.SqlClient;
namespace insurer
{
/// <summary>
/// DataAccess The summary description of.
/// </summary>
public class DataAccess
{
private readonly string SQLCONNECTSTR = "server=(local);uid=sa;pwd=lwrong;database=insurer";
private SqlConnection dbConnection;
private readonly string RETUENVALUE = "RETURNVALUE";
// Decide whether to start the transaction
private bool startrans = false;
// To solve the problem of multiple data import , A transaction property that has been added
private SqlTransaction trans = null;
// Defines whether to start a transaction property
public bool StartTrans
{
get
{
return startrans;
}
set
{
startrans = value;
}
}
// Define the transaction
public SqlTransaction Trans
{
get
{
return trans;
}
set
{
if (value != null)
{
trans = value;
}
}
}
// Create open dbConnection object
public void OpenConnection()
{
if ( dbConnection == null )
{
dbConnection = new SqlConnection(SQLCONNECTSTR);
}
if ( dbConnection.State == ConnectionState.Closed )
{
try
{
dbConnection.Open();
}
catch(Exception ex)
{
SystemError.SystemLog(ex.Message);
}
finally
{
}
}
}
// The release of dbConnection object
public void CloseConnection()
{
if (dbConnection != null)
{
if (dbConnection.State == ConnectionState.Open)
{
dbConnection.Dispose();
dbConnection = null;
}
}
}
//
// create cmd, Pay attention to dbconnection Created in this function, but not released in this function.
// In a proper object-oriented design approach, whoever created the object should be responsible for releasing it. In this view, the process is somewhat unsafe !!!!
private SqlCommand CreateCommand(string ProName,SqlParameter[] prams)
{
OpenConnection();
SqlCommand cmd = new SqlCommand(ProName,dbConnection);
cmd.CommandType = CommandType.StoredProcedure;
// If you do a transaction , So for cmd the Transaction Transaction assignment for
if (StartTrans)
{
cmd.Transaction = Trans;
}
if ( prams != null)
{
foreach(SqlParameter parameter in prams)
{
cmd.Parameters.Add(parameter);
}
}
//cmd.Parameters.Add(
return cmd;
}
/// <summary>
/// create cmd, And perform the appropriate actions. And then release cmd!
///
/// This function is execution cmd A method with no return value and no arguments.
/// </summary>
/// <param name="ProName"></param>
public bool RunProc(string ProName)
{
SqlCommand cmd = CreateCommand(ProName,null);
bool k = false;
try
{
k = (bool)cmd.ExecuteScalar();
}
catch(Exception ex)
{
SystemError.SystemLog(ex.Message);
}
finally
{
cmd.Dispose();
}
return k;
}
/// <summary>
/// create cmd, And perform the appropriate actions. And then release cmd!
///
/// This function is execution cmd No return value , But methods that have parameters.
/// </summary>
/// <param name="ProName"></param>
/// <param name="prams"></param>
public bool RunProc(string ProName,SqlParameter[] prams)
{
SqlCommand cmd = CreateCommand(ProName,prams);
bool k = false;
try
{
k = (bool) cmd.ExecuteScalar();
}
catch(Exception ex)
{
SystemError.SystemLog(ex.Message);
}
finally
{
cmd.Dispose();
//Close();
}
return k;
}
/// <summary>
/// create cmd, And perform the appropriate actions. And then release cmd!
///
/// This function is execution cmd With return value , But a method with no parameters.
/// </summary>
/// <param name="ProName"></param>
/// <param name="dataReader"></param>
public void RunProc(string ProName,out SqlDataReader dataReader)
{
SqlCommand cmd = CreateCommand(ProName,null);
dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
try
{
}
catch(Exception ex)
{
SystemError.SystemLog(ex.Message);
}
finally
{
cmd.Dispose();
}
}
/// <summary>
/// create cmd, And perform the appropriate actions. And then release cmd!
///
/// This function is execution cmd With return value , Methods with parameters.
/// </summary>
/// <param name="ProName"></param>
/// <param name="prams"></param>
/// <param name="dataReader"></param>
public void RunProc(string ProName,SqlParameter[] prams,out SqlDataReader dataReader)
{
SqlCommand cmd = CreateCommand(ProName,prams);
dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
try
{
}
catch(Exception ex)
{
SystemError.SystemLog(ex.Message);
}
finally
{
cmd.Dispose();
}
}
/// <summary>
/// create cmd The parameters of the
/// The idea of this method is conditional generation 1 a SqlParameter Object.
/// After the object is generated, a return value type is assigned to the object
/// </summary>
/// <param name="ParamName"></param>
/// <param name="DbType"></param>
/// <param name="size"></param>
/// <param name="direction"></param>
/// <param name="Value"></param>
/// <returns></returns>
public SqlParameter CreateParam(string ParamName, SqlDbType DbType, int size,ParameterDirection direction,object Value)
{
SqlParameter param;
if (size > 0)
{
param = new SqlParameter(ParamName,DbType,size);
}
else
{
param = new SqlParameter(ParamName,DbType);
}
param.Direction = direction;
param.Value = Value;
return param;
}
/// <summary>
/// create cmd Input parameters of
/// </summary>
/// <param name="ParamName"></param>
/// <param name="DbType"></param>
/// <param name="size"></param>
/// <param name="Value"></param>
/// <returns></returns>
public SqlParameter CreateInParam(string ParamName, SqlDbType DbType, int size, object Value)
{
return CreateParam(ParamName,DbType,size,ParameterDirection.Input,Value);
}
/// <summary>
/// create cmd Output parameter of
/// </summary>
/// <param name="ParamName"></param>
/// <param name="DbType"></param>
/// <param name="size"></param>
/// <returns></returns>
public SqlParameter CreateOutParam(string ParamName, SqlDbType DbType, int size)
{
return CreateParam(ParamName,DbType,size,ParameterDirection.Output,null);
}
/// <summary>
/// create cmd Parameter with a return value
/// </summary>
/// <param name="ParamName"></param>
/// <param name="DbType"></param>
/// <param name="size"></param>
/// <returns></returns>
public SqlParameter CreateReturnParam(string ParamName,SqlDbType DbType, int size)
{
return CreateParam(ParamName,DbType,size,ParameterDirection.ReturnValue,null);
}
// start 1 A transaction
public void BeginTrans()
{
OpenConnection();
Trans = dbConnection.BeginTransaction(IsolationLevel.Serializable);
}
public void Commit()
{
if (Trans != null)
{
Trans.Commit();
}
}
public void Rollback()
{
if (Trans != null)
{
Trans.Rollback();
}
}
}
}