C# Access的简单用法个人总结

来源：互联网发布：java语言入门电子书编辑：程序博客网时间：2024/05/16 06:54

/// <summary>

/// 判断表字段类型和长度是否符合要求
/// </summary>
/// <param name="strTableName">表名</param>
/// <param name="strFieldName">字段名</param>
/// <param name="dbType">需求字段类型</param>
/// <param name="iMaxLength">需求字段长度</param>
/// <returns></returns>
public static bool IsFieldDataTypeRight(String strTableName, String strFieldName, OleDbType dbtype, int iMaxLength)
{
try
{
if (dbConection.State == ConnectionState.Closed)
{
dbConection.Open();
}
//获取到strTableName对应的字段
DataTable dt = dbConection.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null, strTableName, null });
if (dt != null && dt.Rows.Count > 0)
{
DataRow drField = dt.NewRow();
foreach (DataRow dr in dt.Rows)
{
if (dr["COLUMN_NAME"].ToString() == strFieldName)//表中存在字段名
{
drField.ItemArray = dr.ItemArray;
break;
}
}
if (drField["COLUMN_NAME"] != null)//如果有字段
{
if ((OleDbType)drField["DATA_TYPE"] == dbtype)
{
if ((OleDbType)drField["DATA_TYPE"] == OleDbType.WChar)//当字段是varchar类型的时候，要判断长度，其他字段类型不做长度判断，另外0长度是memo属性，要特殊判断
{
if (Int32.Parse(drField["CHARACTER_MAXIMUM_LENGTH"].ToString()) == 0
&& Int32.Parse(drField["CHARACTER_OCTET_LENGTH"].ToString()) == 0
&& iMaxLength == 0)
{
return true;
}
if (Int32.Parse(drField["CHARACTER_MAXIMUM_LENGTH"].ToString()) == iMaxLength)
{
return true;
}
else
{
return false;
}
}
return true;
}
}
}
return false;
}
catch
{
return false;
}

}

/// <summary>
/// 增加表的字段
/// </summary>
/// <param name="strTableName"></param>
/// <param name="strFieldName"></param>
/// <param name="dbType"></param>
/// <returns></returns>
public static bool AddTableField(String strTableName, String strFieldName, DbType dbType)
{
try
{
if (dbConection.State == ConnectionState.Closed)
{
dbConection.Open();
}

String strSql = String.Empty;
switch (dbType)
{
case DbType.String:
strSql = String.Format("alter table {0} add column {1} varchar(255)", strTableName, strFieldName);
break;
case DbType.Int32:
strSql = String.Format("alter table {0} add column {1} Integer", strTableName, strFieldName);
break;
case DbType.Boolean:
strSql = String.Format("alter table {0} add column {1} bit", strTableName, strFieldName);
break;
case DbType.DateTime:
strSql = String.Format("alter table {0} add column {1} datetime", strTableName, strFieldName);
break;
default:
break;
}

OleDbCommand myCmd = new OleDbCommand(strSql, dbConection);
myCmd.ExecuteNonQuery();

return true;
}
catch
{
return false;
}
}

/// <summary>
/// 增加表的字段
/// </summary>
/// <param name="strTableName"></param>
/// <param name="strFieldName"></param>
/// <param name="dbType"></param>
/// <returns></returns>
public static bool AddTableField(String strTableName, String strFieldName, OleDbType dbType, int iMaxLength)
{
try
{
if (dbConection.State == ConnectionState.Closed)
{
dbConection.Open();
}

String strSql = String.Empty;
switch (dbType)
{
case OleDbType.WChar:
if (iMaxLength == 0)//memo的长度是0
{
strSql = String.Format("alter table {0} add column {1} memo", strTableName, strFieldName);
}
else
{
strSql = String.Format("alter table {0} add column {1} varchar({2})", strTableName, strFieldName, iMaxLength);
}
break;
case OleDbType.Integer:
strSql = String.Format("alter table {0} add column {1} Integer", strTableName, strFieldName);
break;
case OleDbType.Boolean:
strSql = String.Format("alter table {0} add column {1} bit", strTableName, strFieldName);
break;
case OleDbType.Date:
strSql = String.Format("alter table {0} add column {1} datetime", strTableName, strFieldName);
break;
default:
break;
}

OleDbCommand myCmd = new OleDbCommand(strSql, dbConection);
myCmd.ExecuteNonQuery();

return true;
}
catch
{
return false;
}
}

public static bool UpdateTableField(String strTableName, String strFieldName, OleDbType dbType, int iMaxLength)
{
try
{
if (dbConection.State == ConnectionState.Closed)
{
dbConection.Open();
}

String strSql = String.Empty;
switch (dbType)
{
case OleDbType.WChar:
if (iMaxLength == 0)
{
strSql = String.Format("alter table {0} alter column {1} memo", strTableName, strFieldName);
}
else
{
strSql = String.Format("alter table {0} alter column {1} varchar({2})", strTableName, strFieldName, iMaxLength);
}
break;
case OleDbType.BSTR://暂用BSTR代替备注类型
strSql = String.Format("alter table {0} alter column {1} memo", strTableName, strFieldName);
break;
case OleDbType.Integer:
strSql = String.Format("alter table {0} alter column {1} Integer", strTableName, strFieldName);
break;
case OleDbType.Boolean:
strSql = String.Format("alter table {0} alter column {1} bit", strTableName, strFieldName);
break;
case OleDbType.Date:
strSql = String.Format("alter table {0} alter column {1} datetime", strTableName, strFieldName);
break;
default:
break;
}

OleDbCommand myCmd = new OleDbCommand(strSql, dbConection);
myCmd.ExecuteNonQuery();

return true;
}
catch
{
return false;
}
}

/// <summary>
/// 判断记录是否存在
/// </summary>
/// <param name="strTableName"></param>
/// <param name="strRecordName"></param>
/// <param name="strRecordValue"></param>
/// <returns></returns>
public static bool IsRecordExist(String strTableName, String strRecordName, String strRecordValue)
{
try
{
if (dbConection.State == ConnectionState.Closed)
{
dbConection.Open();
}

String strSql = String.Format("select * from {0} where {1} = '{2}'",
strTableName, strRecordName, strRecordValue);

OleDbDataAdapter da;
DataTable dt;
GetDBData(strSql, out da, out dt);
if (dt.Rows.Count > 0)
{
return true;
}
else
{
return false;
}
}
catch
{
return false;
}
}

/// <summary>
/// 删除表字段
/// </summary>
/// <param name="strTableName"></param>
/// <param name="strField"></param>
/// <returns></returns>
public static bool DeleteTableField(string strTabName, string strField)
{
try
{
String strSql = string.Format("Alter table {0} drop column {1}", strTabName, strField);

return DBCommon.ExecuteSql(strSql);
}
catch
{
return false;
}
}

0 0