SqlServerCe 数据库操作示例

 

代码

using System;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlServerCe;
using System.IO;
using System.Text;


namespace SmartDeviceTest1
{
static class Program
{
/// <summary>
/// 应用程序的主入口点。
/// </summary>
[MTAThread]
static void Main()
{

CreatDB();

// Application.Run(new Form1());
}

public static void CreatDB()
{
SqlCeConnection conn = null ;
try
{
if (File.Exists( " Test.sdf " )) File.Delete( " Test.sdf " );
SqlCeEngine engine = new SqlCeEngine( " Data Source = Test.sdf " );
engine.CreateDatabase();

conn = new SqlCeConnection( " Data Source = Test.sdf " );
conn.Open();

SqlCeCommand cmd = conn.CreateCommand();
cmd.CommandText = " CREATE TABLE TestTbl(col1 int PRIMARY KEY, col2 ntext, col3 money) " ;

cmd.ExecuteNonQuery();

cmd.CommandText = " INSERT INTO TestTbl(col1, col2, col3) VALUES (0, 'abc', 15.66) " ;

cmd.ExecuteNonQuery();

cmd.CommandText = " INSERT INTO TestTbl(col1, col2, col3) VALUES (?, ?, ?) " ;

cmd.Parameters.Add( new SqlCeParameter( " p1 " , SqlDbType.Int));
cmd.Parameters.Add( new SqlCeParameter( " p2 " , SqlDbType.NText));
cmd.Parameters.Add( new SqlCeParameter( " p3 " , SqlDbType.Money));
cmd.Parameters[ " p2 " ].Size = 50 ;
cmd.Prepare();
cmd.Parameters[ " p1 " ].Value = 1 ;
cmd.Parameters[ " p2 " ].Value = " abc " ;
cmd.Parameters[ " p3 " ].Value = 15.66 ;
cmd.ExecuteNonQuery();

cmd.Parameters.Clear();

cmd.CommandText = " SELECT * FROM TestTbl " ;

SqlCeDataReader rdr = cmd.ExecuteReader();

while (rdr.Read())
{
MessageBox.Show( " col1 = " + rdr.GetInt32( 0 ) +
" col2 = " + rdr.GetString( 1 ) +
" col3 = " + rdr.GetSqlMoney( 2 ));
}


cmd.CommandText = " UPDATE TestTbl SET col2 = 'some new value' WHERE col1 = 0 " ;
cmd.ExecuteNonQuery();

cmd.CommandText = " SELECT * FROM TestTbl " ;
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
MessageBox.Show( " col1 = " + rdr.GetInt32( 0 ) +
" col2 = " + rdr.GetString( 1 ) +
" col3 = " + rdr.GetSqlMoney( 2 ));
}


} // end try
catch (SqlCeException ee)
{
ShowErrors(ee);
}
finally
{
if (conn.State == ConnectionState.Open)
conn.Close();
}
}

public static void ShowErrors(SqlCeException e)
{
SqlCeErrorCollection errorCollection = e.Errors;
StringBuilder bld = new StringBuilder();
foreach (SqlCeError err in errorCollection)
{
bld.Append( " \n Error Code: " + err.HResult.ToString( " X " ));
bld.Append( " \n Message : " + err.Message);
bld.Append( " \n Minor Err.: " + err.NativeError);
bld.Append( " \n Source: " + err.Source);
foreach ( int numPar in err.NumericErrorParameters)
{
if ( 0 != numPar) bld.Append( " \n Num. Par. : " + numPar);
}
foreach ( string errPar in err.ErrorParameters)
{
if (String.Empty != errPar) bld.Append( " \n Err. Par. : " + errPar);
}
MessageBox.Show(bld.ToString());
bld.Remove( 0 , bld.Length);
}
}

}


}

 

转载于:https://www.cnblogs.com/fightLonely/archive/2010/05/13/1734691.html